USE SGLDBMP01;
GO

-- SELECT * FROM dbo.[Category] (NoLock);
-- Ref: https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/

/* Unit Test Script:

SELECT * FROM dbo.[Category] (NoLock);

OP Execute Query:
-----------------
Stored Proc with the name: 'dbo.sProc_GET_Category' already exists, hence dropped
Stored Procedure: sProc_GET_Category, created successfully

Positive Condition:
-------------------
Exec dbo.sProc_GET_Category 'Y'

Negative Condition:
-------------------
Exec dbo.sProc_GET_Category 'y'	-- (11 row(s) affected)

Exec dbo.sProc_GET_Category 'N' -- (0 row(s) affected)

Exec dbo.sProc_GET_Category 
	Msg 201, Level 16, State 4, Procedure sProc_GET_Category, Line 0
	Procedure or function 'sProc_GET_Category' expects parameter '@IsActive', which was not supplied.

Exec dbo.sProc_GET_Category 200	-- (0 row(s) affected)

Exec dbo.sProc_GET_Category 0.5
	Msg 8114, Level 16, State 1, Procedure sProc_GET_Category, Line 0
	Error converting data type numeric to char.

Exec dbo.sProc_GET_Category -5	-- (0 row(s) affected)

Exec dbo.sProc_GET_Category ''	-- (0 row(s) affected)

Exec dbo.sProc_GET_Category '44'-- (0 row(s) affected)	

*/


IF OBJECT_ID('dbo.sProc_GET_Category', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.sProc_GET_Category;
	PRINT 'Stored Proc with the name: ''dbo.sProc_GET_Category'' already exists, hence dropped';
END;
GO
 
CREATE PROCEDURE dbo.sProc_GET_Category
	@IsActive CHAR(1)
AS
BEGIN

  BEGIN TRY

	SELECT CategoryId
		, CategoryName
	FROM dbo.Category (NoLock)
	WHERE IsActive = @IsActive;

  END TRY
  BEGIN CATCH
 
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
 
    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
 
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

  END CATCH
END;
GO

IF OBJECT_ID(N'[dbo].[sProc_GET_Category]', N'P') IS NOT NULL
    PRINT 'Stored Procedure: ''dbo.sProc_GET_Category'', created successfully';
GO
